想舉例子並做筆記整理sql-server多種更新方式
假如大大們有其他資料庫或是更好、其他作法都可以提出討論。

舉例:

目前發現有玩家利用程式漏洞洗遊戲點數

資料表:

玩家表格

訂單

附註:

• 遊戲幣值是1:1

判斷方式:

遊戲歷史總點數>訂單總金額
代表該玩家作弊所以封鎖帳號

更新方式 update select from

格式:

update T
set 欄位 = 更新值
from (
    select 欄位 from 表格
) T

舉例SQL跟邏輯:

update T
set 是否停權 = 'Y'
from (
	select T2.總金額,T2.* from (
		--先整理玩家訂單總金額
		select 玩家ID,sum(金額) 總金額 from 訂單 T1
		group by 玩家ID 
	) T1
	left join 玩家表格 T2 on T1.玩家ID = T2.玩家ID
	--藉由玩家歷史總點數 > 訂單總金額,判斷出哪些是作弊玩家
	where T2.歷史總點數 > T1.總金額 
) T

Demo Link

更新方式 CTE + update

with CTE as (
    select 欄位 from 表格
)
update CTE
set 欄位 = 更新值

舉例SQL跟邏輯:

with CTE as (
	select T1.總金額,T2.* from (
		--先整理玩家訂單總金額
		select 玩家ID,sum(金額) 總金額 from 訂單 T1
		group by 玩家ID 
	) T1
	left join 玩家表格 T2 on T1.玩家ID = T2.玩家ID
	--藉由玩家歷史總點數 > 訂單總金額,判斷出哪些是作弊玩家
	where T2.歷史總點數 > T1.總金額 
)
--先取消備份在更新
--select * into 備份DB..CTE_日期 from CTE;
update CTE
set 是否停權 = 'Y'

Demo Link

更新方式 update from join

格式:

UPDATE
    T
SET
    Table_A.欄位1 = Table_B.欄位1,
    Table_A.欄位2 = Table_B.欄位2
FROM
    表格A AS T
    INNER JOIN 表格B AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.欄位3 = 'xxx'

舉例SQL跟邏輯:

update T2
set T2.是否停權 = 'Y'
from (
	--先整理玩家訂單總金額
	select 玩家ID,sum(金額) 總金額 from 訂單 T1
	group by 玩家ID 
) T1
left join 玩家表格 T2 on T1.玩家ID = T2.玩家ID
--藉由玩家歷史總點數 > 訂單總金額,判斷出哪些是作弊玩家
where T2.歷史總點數 > T1.總金額 

Demo Link

比起上兩個方式少一層查詢
但測試查詢時比較不方便、但效能好

更新方式 Merge

格式:

MERGE INTO 表格A T
   USING 表格B S 
   ON 條件篩選
WHEN MATCHED THEN
   UPDATE 
      SET 表格A.欄位 = 更新值;

舉例SQL跟邏輯:

MERGE INTO 玩家表格 T
   USING (
        select 玩家ID,sum(金額) 總金額 from 訂單 T1
        group by 玩家ID    
   ) S 
   ON T.玩家ID = S.玩家ID and T.歷史總點數 > S.總金額
WHEN MATCHED THEN
   UPDATE 
      SET 是否停權 = 'Y';

Merge除了更新外還可以新增、刪除等操作。
注意只支援2008以上版本!

DEMO LINK

小經驗

我個人習慣update select from或是CTE + update
在更新前可以方便備份資料
如:

with CTE as (
    ...
)
--先備份在更新
select * into 備份DB..CTE_日期 from CTE;
--再註解備份再更新
--update CTE
--set ....

也方便做測試查詢。

參考資料:

How do I UPDATE from a SELECT in SQL Server? - Stack Overflow

測試DDL

--測試DDL
CREATE TABLE 玩家表格(
   玩家ID  NVARCHAR(4) NOT NULL PRIMARY KEY
  ,玩家名稱  NVARCHAR(5) NOT NULL
  ,歷史總點數 INTEGER  NOT NULL
  ,目前點數  INTEGER  NOT NULL
  ,是否停權  VARCHAR(1) NOT NULL
);
INSERT INTO 玩家表格(玩家ID,玩家名稱,歷史總點數,目前點數,是否停權) VALUES ('P001',N'IT邦大雄',100,50,'N');
INSERT INTO 玩家表格(玩家ID,玩家名稱,歷史總點數,目前點數,是否停權) VALUES ('P002',N'IT邦胖虎',9999,100,'N');
INSERT INTO 玩家表格(玩家ID,玩家名稱,歷史總點數,目前點數,是否停權) VALUES ('P003',N'IT邦小夫',10000,0,'N');

CREATE TABLE 訂單(
   訂單ID NVARCHAR(4)  NOT NULL PRIMARY KEY 
  ,玩家ID NVARCHAR(4) NOT NULL
  ,金額   INTEGER  NOT NULL
);
INSERT INTO 訂單(訂單ID,玩家ID,金額) VALUES ('0001','P001',100);
INSERT INTO 訂單(訂單ID,玩家ID,金額) VALUES ('0002','P002',500);
INSERT INTO 訂單(訂單ID,玩家ID,金額) VALUES ('0003','P003',5000);
INSERT INTO 訂單(訂單ID,玩家ID,金額) VALUES ('0004','P003',5000);